VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   9225
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   11550
   LinkTopic       =   "Form1"
   ScaleHeight     =   9225
   ScaleWidth      =   11550
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdClonarPlan 
      Caption         =   "cmdClonarPlan"
      Height          =   1215
      Left            =   6480
      TabIndex        =   22
      Top             =   5520
      Width           =   2415
   End
   Begin VB.CommandButton Command2 
      Caption         =   "Agency"
      Height          =   855
      Left            =   9360
      TabIndex        =   21
      Top             =   4320
      Width           =   1935
   End
   Begin VB.CommandButton cmdConsolidarAgencias 
      Caption         =   "ConsolidarAgencias"
      Height          =   735
      Left            =   4320
      TabIndex        =   20
      Top             =   6120
      Width           =   1575
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Command1"
      Height          =   735
      Left            =   10080
      TabIndex        =   19
      Top             =   3120
      Width           =   1215
   End
   Begin VB.TextBox txtDouble 
      Height          =   615
      Left            =   8400
      TabIndex        =   17
      Top             =   2520
      Width           =   975
   End
   Begin VB.CommandButton cmdSuma 
      Caption         =   "cmdSuma0.78"
      Height          =   615
      Left            =   9720
      TabIndex        =   16
      Top             =   1920
      Width           =   1575
   End
   Begin VB.CommandButton cmdProduct 
      Caption         =   "product"
      Height          =   1095
      Left            =   7920
      TabIndex        =   15
      Top             =   240
      Width           =   3255
   End
   Begin VB.CommandButton cmdModificar 
      Caption         =   "Modificar"
      Height          =   615
      Left            =   840
      TabIndex        =   14
      Top             =   8520
      Width           =   3735
   End
   Begin VB.CommandButton cmdAvanzar 
      Caption         =   "Avanzar"
      Height          =   615
      Left            =   6240
      TabIndex        =   13
      Top             =   8400
      Width           =   3855
   End
   Begin VB.TextBox txtAgecodage 
      Height          =   735
      Left            =   5280
      TabIndex        =   12
      Top             =   7440
      Width           =   6015
   End
   Begin VB.TextBox txtageapenomxls 
      Height          =   735
      Left            =   360
      TabIndex        =   11
      Top             =   7440
      Width           =   4695
   End
   Begin VB.CommandButton cmdUnificarNombredeAgente 
      Caption         =   "Unificar Nombre de Agentes"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   735
      Left            =   4200
      TabIndex        =   10
      Top             =   5160
      Width           =   1695
   End
   Begin VB.CommandButton cmdSacarLLaves 
      Caption         =   "Sacar  [llaves ] "
      Height          =   615
      Left            =   480
      TabIndex        =   9
      Top             =   6240
      Width           =   3615
   End
   Begin VB.CommandButton cmdRemplazaAgentes 
      Caption         =   "Cambio de agentes"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   13.5
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   615
      Left            =   480
      TabIndex        =   8
      Top             =   5160
      Width           =   3375
   End
   Begin VB.TextBox txtCambia 
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   13.5
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   615
      Left            =   5280
      TabIndex        =   5
      Top             =   4320
      Width           =   3615
   End
   Begin VB.TextBox txtPermanece 
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   13.5
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   615
      Left            =   1200
      TabIndex        =   4
      Top             =   4320
      Width           =   2655
   End
   Begin VB.CommandButton cmdDepurarAgentes 
      Caption         =   "Depurar agetnes"
      Height          =   975
      Left            =   4800
      TabIndex        =   3
      Top             =   2160
      Width           =   2535
   End
   Begin VB.CommandButton cmdCargarAgentes 
      Caption         =   "Cargar Agentes"
      Height          =   855
      Left            =   720
      TabIndex        =   2
      Top             =   2040
      Width           =   2895
   End
   Begin VB.CommandButton cmdFreciaDePago 
      Caption         =   "Cargar Frecuencia de Pago"
      Height          =   615
      Left            =   4320
      TabIndex        =   1
      Top             =   720
      Width           =   3135
   End
   Begin VB.CommandButton cmdCargarPersonas 
      Caption         =   "CargarPersonas"
      Height          =   735
      Left            =   600
      TabIndex        =   0
      Top             =   600
      Width           =   2895
   End
   Begin VB.Label lblDouble 
      Caption         =   "Label1"
      Height          =   495
      Left            =   8160
      TabIndex        =   18
      Top             =   1800
      Width           =   1335
   End
   Begin VB.Label lblCambia 
      Caption         =   "Cambia"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   18
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   5280
      TabIndex        =   7
      Top             =   3600
      Width           =   3255
   End
   Begin VB.Label lblPermanece 
      Caption         =   "Permanece"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   18
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   1200
      TabIndex        =   6
      Top             =   3600
      Width           =   2535
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim agenteid As Integer

Private Sub cmdCargarAgentes_Click()

Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim agenteid As Integer

agenteid = 1
ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

query = "select polcodpol,polizaid from poliza order by polizaid"
Rs.Open query, Conexion, adOpenStatic, adLockReadOnly

Rs.MoveFirst

Do While Not Rs.EOF

      query = "select ifa from liqant where cuenta = '" & Rs!polcodpol & "'"
      rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
      rs1.MoveFirst
      
      
      query = "insert into agentes (agenteid ,ageapenomxls, ageapenomfid) values ('" & agenteid & "','" & Trim(rs1!ifa) & "',' ')"
      rs2.Open query, Conexion, adOpenStatic, adLockReadOnly
      
      query = "update poliza set agenteid = '" & Trim(agenteid) & "' where polizaID = " & Rs!polizaID
      rs2.Open query, Conexion, adOpenStatic, adLockReadOnly

agenteid = agenteid + 1
rs1.Close
Rs.MoveNext
Loop


End Sub

Private Sub cmdClonarPlan_Click()

Dim CodPlanOriginal As String
Dim CodPlanclonado As String


CodPlanOriginal = "00000000003"


CodPlanclonado = "ULPLATA"

If ClonarPlan(CodPlanOriginal, CodPlanclonado) Then
    
End If
End Sub

Private Sub cmdConsolidarAgencias_Click()
Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset


Dim frecuencia As String
Dim contador As Integer
Dim ageoriginal As String
Dim agenteidoriginal As String

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

query = "SELECT agcyname,agcycodagcy FROM agency order by agencyid"
'Rs.Close
Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
Rs.MoveFirst

Do While Not Rs.EOF


query = "select cuenta from liqant where agencia = '" & Rs!agcyname & "'"
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly

Do While Not rs1.EOF
          
   query = "update policy agcycodagcy set agcycodagcy = '" & Rs!agcycodagcy & "' where polcodpol = '" & rs1!cuenta & "'"
   rs2.Open query, Conexion, adOpenStatic, adLockReadOnly
rs1.MoveNext
Loop
rs1.Close


Rs.MoveNext
Loop

End Sub

Private Sub cmdDepurarAgentes_Click()

Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim MENSUAL As Integer
Dim TRIMESTRAL As Integer
Dim SEMESTRAL As Integer
Dim ANUAL As Integer
Dim frecuencia As String
Dim contador As Integer
Dim ageoriginal As String
Dim agenteidoriginal As String

ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

For contador = 0 To 4119
     
     query = "select agenteid, ageapenomxls from agentes where agenteid > " & contador & " order by agenteid"
     Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
     Rs.MoveFirst

ageroriginal = Trim(Rs!ageapenomxls)
agenteidoriginal = Trim(Rs!agenteid)
Rs.MoveNext

     Do While Not Rs.EOF
     
     
        If ageroriginal = Trim(Rs!ageapenomxls) Then
           query = "update poliza set agenteid = '" & agenteidoriginal & "' where agenteid = '" & Rs!agenteid & "'"
           borraragenteid = Rs!agenteid
           
           rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
          
         
           query = "delete from agentes where agenteid = '" & borraragenteid & "'"
           rs2.Open query, Conexion, adOpenStatic, adLockReadOnly
                    
         
         
         
        End If
     Rs.MoveNext
     Loop

Rs.Close
Next


End Sub

Private Sub cmdFreciaDePago_Click()

Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim MENSUAL As Integer
Dim TRIMESTRAL As Integer
Dim SEMESTRAL As Integer
Dim ANUAL As Integer
Dim frecuencia As String



ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset


query = "select polcodpol,polizaid, FRECUENCIA  from poliza where frecuencia = 'SINDATOS' ORDER BY POLIZAID"
Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
Rs.MoveFirst

Do While Not Rs.EOF


query = "select plan from liqant where cuenta = '" & Rs!polcodpol & "'"
rs1.Close
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
rs1.MoveFirst


  Do While Not rs1.EOF


   If Trim(rs1!Plan) = "MENSUAL" Then MENSUAL = MENSUAL + 1
   If Trim(rs1!Plan) = "mensual" Then MENSUAL = MENSUAL + 1
   If Trim(rs1!Plan) = "Mensual" Then MENSUAL = MENSUAL + 1
    
    
   If Trim(rs1!Plan) = "TRIMESTRAL" Then TRIMESTRAL = TRIMESTRAL + 1
   If Trim(rs1!Plan) = "trimestral" Then TRIMESTRAL = TRIMESTRAL + 1
   If Trim(rs1!Plan) = "Trimestral" Then TRIMESTRAL = TRIMESTRAL + 1
  

   If Trim(rs1!Plan) = "SEMESTRAL" Then SEMESTRAL = SEMESTRAL + 1
   If Trim(rs1!Plan) = "semestral" Then SEMESTRAL = SEMESTRAL + 1
   If Trim(rs1!Plan) = "Semestral" Then SEMESTRAL = SEMESTRAL + 1
    
    
  If Trim(rs1!Plan) = "ANUAL" Then ANUAL = ANUAL + 1
  If Trim(rs1!Plan) = "anual" Then ANUAL = ANUAL + 1
  If Trim(rs1!Plan) = "Anual" Then ANUAL = ANUAL + 1
  If Trim(rs1!Plan) = "ANNUAL" Then ANUAL = ANUAL + 1
  

 rs1.MoveNext
 Loop
frecuencia = "SINDATOS"
If MENSUAL > TRIMESTRAL And MENSUAL > SEMESTRAL And MENSUAL > ANUAL Then frecuencia = "MENSUAL"
If TRIMESTRAL > MENSUAL And TRIMESTRAL > SEMESTRAL And TRIMESTRAL > ANUAL Then frecuencia = "TRIMESTRAL"
If SEMESTRAL > MENSUAL And SEMESTRAL > TRIMESTRAL And SEMESTRAL > ANUAL Then frecuencia = "SEMESTRAL"
If ANUAL > MENSUAL And ANUAL > TRIMESTRAL And ANUAL > SEMESTRAL Then frecuencia = "ANUAL"

query = "update poliza set frecuencia = '" & Trim(frecuencia) & "' where polizaID = " & Rs!polizaID
rs2.Open query, Conexion, adOpenStatic, adLockReadOnly


MENSUAL = 0
TRIMESTRAL = 0
SEMESTRAL = 0
ANUAL = 0
frecuencia = "SINDATOS"

Rs.MoveNext
Loop

End Sub

Private Sub Command1_Click()
Dim testVar As String
Dim esdouble As Double
Dim numericCheck As Boolean
testVar = "53"
' The following call to IsNumeric returns True.
numericCheck = IsNumeric(testVar)
testVar = "-459.95"
esdouble = Val(testVar)
' The following call to IsNumeric returns True.
numericCheck = IsNumeric(testVar)
testVar = "45 Help"
' The following call to IsNumeric returns False.
numericCheck = IsNumeric(testVar)

End Sub

Private Sub Label2_Click()

End Sub

Private Sub cmdProduct_Click()
Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

'query = "select * from productlevel where procodpro = '00000000002' and proyearfinal < 100 order by productlevelid "

query = "select * from productlevel where procodpro = '00000000009' and proyearfinal >1 and proyearfinal < 110"
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
rs1.MoveFirst

Do While Not rs1.EOF

'query = "insert into productlevel (procodpro,proagencylevel,proyearinicial,proyearfinal,propercent) values " & _
        "('00000000016','" & rs1!proagencylevel & "','" & rs1!proyearinicial & "','" & rs1!proyearfinal & "','" & rs1!propercent & "')"

query = "update productlevel set propercent = '" & rs1!propercent & "' where proyearinicial = '" & rs1!proyearinicial & "'" & _
        " and proyearfinal = '" & rs1!proyearfinal & "' and procodpro = '00000000011' and proagencylevel = '" & rs1!proagencylevel & "'"
        
       ' "('00000000016','" & rs1!proagencylevel & "','" & rs1!proyearinicial & "','" & rs1!proyearfinal & "','" & rs1!propercent & "')"

Rs.Open query, Conexion, adOpenStatic, adLockReadOnly

rs1.MoveNext
Loop


End Sub

Private Sub cmdRemplazaAgentes_Click()
Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Dim frecuencia As String
Dim contador As Integer
Dim ageoriginal As String
Dim agenteidoriginal As String

ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

query = "update poliza set agenteid = '" & Trim(Form1.txtPermanece) & "' where agenteid = '" & Trim(Form1.txtCambia) & "'"
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
          
    
query = "delete from agentes where agenteid = '" & Form1.txtCambia & "'"
rs2.Open query, Conexion, adOpenStatic, adLockReadOnly

Form1.txtCambia = ""
End Sub

Private Sub cmdSacarLLaves_Click()


Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Dim frecuencia As String
Dim contador As Integer
Dim conllave As String
Dim sinllave As String
Dim posicionllave As Integer
Dim agenteid  As Integer



ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset


For contador = 0 To 104
      query = "select agenteid, ageapenomxls from agentes where ageapenomxls like '%[%' order by agenteid"
     Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
     Rs.MoveFirst

agenteid = Rs!agenteid

conllave = Trim(Rs!ageapenomxls)
posicionllave = InStr(conllave, "[") - 1

    If posicionllave > 0 Then
       sinllave = Trim(Left(conllave, posicionllave))
    End If

Rs.Close
query = "update agentes set ageapenomxls = '" & sinllave & "' where agenteid = '" & agenteid & "'"
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly


Next



End Sub

Private Sub cmdUnificarNombredeAgente_Click()

Dim query As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim agenteid As Integer

agenteid = 1
ADOConnect

Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

query = "select ageapenom, agecodage from agentesfid order by ageapenom"
Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
Rs.MoveFirst

txtAgeApeNom.Text = Trim(Rs!ageapenom)
txtAgecodage = Trim(Rs!AgeCodAge)


query = "select ageapenomxls,agenteid from agentes order by ageapenomxls"
rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
rs1.MoveFirst

Do While Not Rs.EOF

  Do While Not rs1.EOF

      If Trim(Rs!ageapenom) = Trim(rs1!ageapenomxls) Then
     
      
      query = "update agentes set ageapenom = '" & Trim(Rs!ageapenom) & "', agecodage = '" & rs1!AgeCodAge & "' where agenteID = " & rs1!agenteid
      rs2.Open query, Conexion, adOpenStatic, adLockReadOnly
           
      End If
      
   rs1.MoveNext
   Loop

rs1.MoveFirst
Rs.MoveNext

Loop


End Sub

Private Sub Command2_Click()

Dim query, agenciacodigo As String
Dim Rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset


Set Rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset


query = "select polcodpol,serviceagent,agcycodagcy  from policy where agcycodagcy = '' AND NOT SERVICEAGENT IS NULL order by polcodpol"

Rs.Open query, Conexion, adOpenStatic, adLockReadOnly
Rs.MoveFirst

Do While Not Rs.EOF

     query = "SELECT agcycodagcy from agent where ageapenom = '" & Rs!ServiceAgent & "'"
     rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
     
     agenciacodigo = ""
     
  If Not IsNull(rs1!agcycodagcy) Then
     
     agenciacodigo = rs1!agcycodagcy
     rs1.Close
     
     query = "update policy agcycodagcy set agcycodagcy = '" & agenciacodigo & "' where ServiceAgent = '" & Rs!ServiceAgent & "' and polcodpol = '" & _
     Rs!polcodpol & "'"
     rs1.Open query, Conexion, adOpenStatic, adLockReadOnly
     Else
     rs1.Close
  End If

Rs.MoveNext
Loop



End Sub

Private Sub Form_Load()
Dim esdouble As Double

esdouble = (15.1) * (-0.5)
lblDouble.Caption = esdouble
esdouble = esdouble * 12

End Sub
